package com.hnkywl.sts.dao.htgl;

import com.hnkywl.sts.dao.base.RegionDao;
import com.hnkywl.sts.dao.base.RegionForBgDao;
import com.hnkywl.sts.entity.htgl.Htmx;
import com.hnkywl.sts.entity.htgl.Xsht;
import net.ruixin.orm.hibernate.Page;
import net.ruixin.orm.hibernate.SimpleHibernateTemplate;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.Criteria;
import org.hibernate.Hibernate;
import org.hibernate.Query;
import org.hibernate.criterion.*;
import org.hibernate.transform.AliasToEntityMapResultTransformer;
import org.hibernate.transform.Transformers;
import org.hibernate.type.Type;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.sql.*;
import java.util.*;
import java.util.Date;

/**
 * Created with IntelliJ IDEA.
 * User: jerry
 * Date: 13-4-8
 * Time: 下午1:33
 * To change this template use File | Settings | File Templates.
 */
@Repository
public class XshtDao extends SimpleHibernateTemplate<Xsht, Long> {
    /**
     * 销售合同的查询
     *
     * @param zt       状态
     * @param xslx     销售类型
     * @param htbh     合同编号
     * @param ssgsId   所属公司Id
     * @param ssbmId   所属部门Id
     * @param hzfId    合作方Id
     * @param pageNo
     * @param pageSize
     * @return
     */
    public Page<Xsht> findXsHtList(String zt, String xslx, String htbh, Long ssgsId, Long ssbmId, Long xsrId, Long hzfId, java.util.Date startDate, java.util.Date endDate, String xhtbm, int pageNo, int pageSize) {
        Page<Xsht> page = new Page<Xsht>();
        page.setAutoCount(true);
        page.setPageNo(pageNo);
        page.setPageSize(pageSize);
        page.setOrder("desc");
        page.setOrderBy("dateCreated");

        Criteria criteria = getSession().createCriteria(this.entityClass)
                .createAlias("zddhcght", "cght", CriteriaSpecification.LEFT_JOIN)
                .createAlias("hzf", "hzf", CriteriaSpecification.LEFT_JOIN)
                .createAlias("wlfgs", "wlfgs", CriteriaSpecification.LEFT_JOIN)
                .createAlias("wlfbm", "wlfbm", CriteriaSpecification.LEFT_JOIN)
                .createAlias("wlfry", "wlfry", CriteriaSpecification.LEFT_JOIN)
                .createAlias("sbgs", "sbgs", CriteriaSpecification.LEFT_JOIN)
                .createAlias("sbbm", "sbbm", CriteriaSpecification.LEFT_JOIN)
                .createAlias("sbr", "sbr", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
                                .add(Property.forName("sbsl").as("sbsl"))
                                .add(Projections.sqlProjection("this_.type as type", new String[]{"type"}, new Type[]{Hibernate.STRING}))
                                .add(Property.forName("id").as("id"))
                                .add(Property.forName("cght.htbm").as("cght_htbm"))
                                .add(Projections.sqlProjection("this_.xsxt_htbm as xsxt_htbm", new String[]{"xsxt_htbm"}, new Type[]{Hibernate.STRING}))
                                .add(Projections.sqlProjection("this_.htbm as htbm", new String[]{"htbm"}, new Type[]{Hibernate.STRING}))
                                .add(Property.forName("hzf.qc").as("hzf_qc"))
                                .add(Projections.sqlProjection("this_.zt as zt", new String[]{"zt"}, new Type[]{Hibernate.STRING}))
                                .add(Property.forName("wlfgs.qc").as("wlfgs_qc"))
                                .add(Property.forName("wlfbm.qc").as("wlfbm_qc"))
                                .add(Property.forName("wlfry.xm").as("wlfry_xm"))
                                .add(Property.forName("sbgs.jc").as("sbgs_jc"))
                                .add(Property.forName("sbbm.qc").as("sbbm_qc"))
                                .add(Property.forName("sbr.xm").as("sbr_xm"))
                                .add(Property.forName("zbcg").as("zbcg"))
                                .add(Property.forName("htms").as("htms"))
                                .add(Property.forName("extension").as("extension"))
                                .add(Projections.sqlProjection("this_.qdrq as qdrq", new String[]{"qdrq"}, new Type[]{Hibernate.DATE}))
                                .add(Property.forName("jsrq").as("jsrq"))
                                .add(Property.forName("wlyl").as("wlyl"))
                                .add(Property.forName("processInstanceId").as("processInstanceId"))
                                .add(Projections.sqlProjection("this_.xslx as xslx", new String[]{"xslx"}, new Type[]{Hibernate.STRING}))
                                .add(Property.forName("dateCreated"))
                )
                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        if (StringUtils.isNotEmpty(xslx)) {
            criteria.add(Restrictions.eq("xslx", xslx));
        }
        if (StringUtils.isNotEmpty(zt)) {
            criteria.add(Restrictions.eq("zt", zt));
        }

        if (startDate != null) {
            criteria.add(Restrictions.ge("qdrq", sDaysAfterNowDate(startDate)));
        }

        if (endDate != null) {
            criteria.add(Restrictions.le("qdrq", jDaysAfterNowDate(endDate)));
        }

        if (StringUtils.isNotEmpty(xhtbm)) {
            criteria.add(Property.forName("xsxt_htbm").like(xhtbm, MatchMode.ANYWHERE));
        }

        if (StringUtils.isNotEmpty(htbh)) {
            criteria.add(Property.forName("htbm").like(htbh, MatchMode.START));
        }

        if (ssgsId != null) {
            criteria.add(Restrictions.eq("sbgs.id", ssgsId));
        }
        if (ssbmId != null) {
            criteria.add(Restrictions.eq("sbbm.id", ssbmId));
        }
        if (xsrId != null) {
            criteria.add(Restrictions.eq("sbr.id", xsrId));
        }
        if (hzfId != null) {
            criteria.add(Restrictions.eq("hzf.id", hzfId));
        }
        RegionDao.createDataRegion(criteria);
        Page list = this.findByCriteria(page, criteria);
        return list;
    }

    //   当前日期加减n天后的日期，返回String   (yyyy-mm-dd)
    private java.util.Date sDaysAfterNowDate(int n) {
        Calendar rightNow = Calendar.getInstance();
        rightNow.add(Calendar.DAY_OF_MONTH, -n);
        return rightNow.getTime();
    }

    //   当前日期加减n天后的日期，返回String   (yyyy-mm-dd)
    public java.util.Date sDaysAfterNowDate(java.util.Date date) {
        Calendar rightNow = Calendar.getInstance();
        rightNow.setTime(date);
        rightNow.set(java.util.Calendar.HOUR_OF_DAY, 0);
        rightNow.set(java.util.Calendar.MINUTE, 0);
        rightNow.set(java.util.Calendar.SECOND, 0);
        return rightNow.getTime();
    }

    //   当前日期加减n天后的日期，返回String   (yyyy-mm-dd)
    public java.util.Date jDaysAfterNowDate(java.util.Date date) {
        Calendar rightNow = Calendar.getInstance();
        rightNow.setTime(date);
        rightNow.set(java.util.Calendar.HOUR_OF_DAY, 23);
        rightNow.set(java.util.Calendar.MINUTE, 59);
        rightNow.set(java.util.Calendar.SECOND, 59);
        return rightNow.getTime();
    }


    public Page findXsHtList(String zt, String khmc, int pageNo, int pageSize) {
        Page<Xsht> page = new Page<Xsht>();
        page.setAutoCount(true);
        page.setPageNo(pageNo);
        page.setPageSize(pageSize);
        page.setOrder("desc");
        page.setOrderBy("dateCreated");
        Criteria criteria = getSession().createCriteria(this.entityClass)
                .createAlias("hzf", "hzf", CriteriaSpecification.LEFT_JOIN)
                .createAlias("xsfy", "xsfy", CriteriaSpecification.LEFT_JOIN)
                .createAlias("lj", "lj", CriteriaSpecification.LEFT_JOIN)
                .createAlias("gc", "gc", CriteriaSpecification.LEFT_JOIN)
                .createAlias("htxz", "htxz", CriteriaSpecification.LEFT_JOIN)
                .createAlias("sbr", "sbr", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
                        .add(Property.forName("id").as("id"))
                        .add(Property.forName("htbm").as("htbm"))
                        .add(Property.forName("xsxt_htbm").as("xhtbm"))
                        .add(Property.forName("htxz.id").as("htxz.id"))
                        .add(Property.forName("htxz.z").as("htxz.z"))
                        .add(Property.forName("lj.id").as("lj_id"))
                        .add(Property.forName("lj.mc").as("lj_mc"))
                        .add(Property.forName("sbr.xm").as("sbr_xm"))
                        .add(Property.forName("lj.ll").as("lj_ll"))
                        .add(Property.forName("lj.kxg").as("lj_kxg"))
                        .add(Property.forName("hzf.id").as("hzf_id"))
                        .add(Property.forName("hzf.qc").as("hzf_qc"))
                        .add(Property.forName("gc.id").as("gc_id"))
                        .add(Property.forName("gc.mc").as("gc_mc"))
                        .add(Property.forName("jhsj").as("jhsj"))
                        .add(Property.forName("jhdd").as("jhdd"))
                        .add(Property.forName("xsfy.z").as("xsfy_z"))
                        .add(Property.forName("sl").as("sl"))
                        .add(Property.forName("fhzr.id").as("fhzr_id"))
                        .add(Property.forName("wlfgs.id").as("wlfgs_id"))
                        .add(Property.forName("wlfbm.id").as("wlfbm_id"))
                        .add(Property.forName("wlfry.id").as("wlfry_id"))
                        .add(Property.forName("qdrq").as("qdrq"))
                        .add(Property.forName("jsrq").as("jsrq")))
                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        if (StringUtils.isNotEmpty(zt))
            criteria.add(Restrictions.eq("zt", zt));
        if (StringUtils.isNotEmpty(khmc))
            criteria.add(Restrictions.like("hzf.jc", "%" + khmc + "%"));
        RegionDao.createDataRegion(criteria);
        return this.findByCriteria(page, criteria);
    }


    public Xsht findXshtByCght(Long cghtId) {
        String sql = "select id from t_biz_ht xsht where xsht.zddhcght_id = ?";
        List<Map> list = (List<Map>) this.createSQLQuery(sql, cghtId).setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE).list();
        List<Xsht> xshts = new ArrayList<Xsht>();
        if (list.size() > 0) {
            for (Map map : list) {
                Xsht x = this.get(Long.valueOf(map.get("ID").toString()));
                xshts.add(x);
            }
            return xshts.get(0);
        } else {
            return null;
        }
    }


    /**
     * 查询销售合同明细
     *
     * @param xshtId 销售合同Id
     * @return
     */
    public Page findXshtmxList(Long xshtId, int pageNo, int pageSize) {
        Page<Htmx> page = new Page<Htmx>();
//      Page<Map> page = new Page<Map>();
        page.setAutoCount(true);
        page.setPageNo(pageNo);
        page.setPageSize(pageSize);
        page.setOrder("desc");
        page.setOrderBy("wzgg.code");
        Criteria criteria = getSession().createCriteria(Htmx.class)
                .createAlias("wzgg", "wzgg", CriteriaSpecification.LEFT_JOIN)
                .createAlias("cd", "cd", CriteriaSpecification.LEFT_JOIN)
                .createAlias("jzfs", "jzfs", CriteriaSpecification.LEFT_JOIN)
                .createAlias("createOrgan", "createOrgan", CriteriaSpecification.LEFT_JOIN)
                .createAlias("createDepartment", "createDepartment", CriteriaSpecification.LEFT_JOIN)
                .createAlias("createUser", "createUser", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
                                .add(Property.forName("createOrgan.id").as("createOrgan.id"))
                                .add(Property.forName("createDepartment.id").as("createDepartment.id"))
                                .add(Property.forName("createUser.id").as("createUser.id"))
                                .add(Property.forName("wzgg.id").as("wzgg.id"))
                                .add(Property.forName("wzgg.code").as("wzgg.code"))
                                .add(Property.forName("wzgg.name").as("wzgg.name"))
                                .add(Property.forName("wzgg.cz").as("wzgg.cz"))
                                .add(Projections.sqlProjection("wzgg1_.ggxh as \"wzgg.ggxh\"", new String[]{"wzgg.ggxh"}, new Type[]{Hibernate.STRING}))
//                              .add(Projections.sqlProjection("wzgg1_.cz||' '||wzgg1_.ggxh as \"wzgg.ggxh\"", new String[]{"wzgg.ggxh"}, new Type[]{Hibernate.STRING}))
                                .add(Property.forName("wzgg.dw").as("wzgg.dw"))
                                .add(Property.forName("cd.id").as("cd.id"))
                                .add(Property.forName("cd.z").as("cd.z"))
                                .add(Property.forName("ck").as("ck"))
                                .add(Property.forName("sbsl").as("sbsl"))
                                .add(Property.forName("sbjs").as("sbjs"))
                                .add(Property.forName("jzfs.id").as("jzfs.id"))
                                .add(Property.forName("jzfs.z").as("jzfs.z"))
                                .add(Property.forName("xsxt_xsdh").as("xsxt_xsdh"))
                                .add(Property.forName("spec").as("spec"))
                                .add(Property.forName("ph").as("ph"))
                                .add(Property.forName("hsj").as("hsj"))
                                .add(Property.forName("bhsj").as("bhsj"))
                                .add(Property.forName("hsje").as("hsje"))
                                .add(Property.forName("bz").as("bz"))
                                .add(Projections.sqlProjection("mxh as \"mxh\"", new String[]{"mxh"}, new Type[]{Hibernate.LONG}))
                ).add(Restrictions.eq("ht.id", xshtId)).add(Restrictions.eq("zt", "ybc"))
                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        criteria.addOrder(Property.forName("mxh").asc());
        return this.findByCriteria(page, criteria);
    }

    public Page findXshtmxListForXsmx(Long xshtId, int pageNo, int pageSize) {
        Page<Htmx> page = new Page<Htmx>();
        page.setAutoCount(true);
        page.setPageNo(pageNo);
        page.setPageSize(pageSize);
        page.setOrder("desc");
        page.setOrderBy("wzgg.code");
        Criteria criteria = getSession().createCriteria(Htmx.class)
                .createAlias("wzgg", "wzgg", CriteriaSpecification.LEFT_JOIN)
                .createAlias("cd", "cd", CriteriaSpecification.LEFT_JOIN)
                .createAlias("jzfs", "jzfs", CriteriaSpecification.LEFT_JOIN)
                .createAlias("createOrgan", "createOrgan", CriteriaSpecification.LEFT_JOIN)
                .createAlias("createDepartment", "createDepartment", CriteriaSpecification.LEFT_JOIN)
                .createAlias("createUser", "createUser", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
                                .add(Property.forName("createOrgan.id").as("createOrgan.id"))
                                .add(Property.forName("createDepartment.id").as("createDepartment.id"))
                                .add(Property.forName("createUser.id").as("createUser.id"))
                                .add(Property.forName("wzgg.id").as("wzgg.id"))
                                .add(Property.forName("wzgg.code").as("wzgg.code"))
                                .add(Property.forName("wzgg.name").as("wzgg.name"))
                                .add(Property.forName("wzgg.cz").as("wzgg.cz"))
                                .add(Projections.sqlProjection("wzgg1_.cz||' '||wzgg1_.ggxh as \"wzgg.ggxh\"", new String[]{"wzgg.ggxh"}, new Type[]{Hibernate.STRING}))
                                .add(Property.forName("wzgg.dw").as("wzgg.dw"))
                                .add(Property.forName("cd.id").as("cd.id"))
                                .add(Property.forName("cd.z").as("cd.z"))
                                .add(Property.forName("ck").as("ck"))
                                .add(Property.forName("sbsl").as("sbsl"))
                                .add(Property.forName("sbjs").as("js"))
                                .add(Property.forName("jzfs.id").as("jzfs.id"))
                                .add(Property.forName("jzfs.z").as("jzfs.z"))
                                .add(Property.forName("ph").as("ph"))
                                .add(Property.forName("hsj").as("hsj"))
                                .add(Property.forName("bhsj").as("bhsj"))
                                .add(Property.forName("hsje").as("hsje"))
                                .add(Property.forName("bz").as("bz"))
                                .add(Projections.sqlProjection("mxh as \"mxh\"", new String[]{"mxh"}, new Type[]{Hibernate.LONG}))
                ).add(Restrictions.eq("ht.id", xshtId)).add(Restrictions.eq("zt", "ybc"))
                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        criteria.addOrder(Property.forName("mxh").asc());
        return this.findByCriteria(page, criteria);
    }

    //销售合同明细合计
    public Map findXshtHj(Long xshtId) {
        String sql = "select sum(sbsl) as \"sbsl\",sum(hsje) as \"hsje\" from t_biz_htmx where ht_id = ?";
        return (Map) createSQLQuery(sql, xshtId).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).uniqueResult();
    }

    //删除明细
    public void deleteHtmx(Long htId) {
        String hql = "update Htmx set deleted=1 where ht.id=?";
        Query query = this.getSession().createQuery(hql);
        query.setLong(0, htId);
        query.executeUpdate();
    }

    // 付款公式校验
    public String checkFkgs(String fkgs) {
        Connection con = null;
        CallableStatement call = null;
        try {
            con = getSession().connection();
            call = con.prepareCall("{Call R_BIZ_CAL(?)}");
            call.setString(1, fkgs);
            call.execute();
            return "0";
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeResource(null, call, con);
        }
        return "1";
    }

    private void closeResource(ResultSet rs, Statement s, Connection con) {
        if (rs != null)
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
                throw new RuntimeException(e.getMessage());
            }
        if (s != null)
            try {
                s.close();
            } catch (SQLException e) {
                e.printStackTrace();
                throw new RuntimeException(e.getMessage());
            }
        if (con != null)
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
                throw new RuntimeException(e.getMessage());
            }
    }

    public Page<Map> findXsmxByKh(String xsxt_xsdh, String carNo, String weight, String bindNo, String bindNos, Long xshtId, int pageNo, int pageSize) {
        Map<String, Object> param = new HashMap<String, Object>();
        List<String> condition = new ArrayList<String>();
        Page<Map> page = new Page<Map>();

        //=====================================

        if (xshtId != null) {
            condition.add(" xsht.id = :xshtId ");
            param.put("xshtId", xshtId);
        }

        if (bindNos != null && !bindNos.isEmpty()) {
            condition.add(" kh.bindNo in (" + bindNos + ") ");
        }

        if (bindNo != null && !bindNo.isEmpty()) {
            condition.add(" kh.bindNo = :bindNo ");
            param.put("bindNo", bindNo);
        }
        if (xsxt_xsdh != null && !xsxt_xsdh.isEmpty()) {
            condition.add(" kh.xsxt_xsdh = :xsxt_xsdh ");
            param.put("xsxt_xsdh", xsxt_xsdh);
        }
        if (carNo != null && !carNo.isEmpty()) {
            condition.add(" kh.carNo = :carNo ");
            param.put("carNo", carNo);
        }
        if (weight != null && !weight.isEmpty()) {
            condition.add(" kh.sl = :weight ");
            param.put("weight", weight);
        }
        //==============记录数量===============
        String sqlCountBase = "select count(1) from ";

        //==============查询结果===============
        String sqlBase = "select kh.khbm \"kh.khbm\",\n" +
                "       kh.id \"kh.id\",\n" +
                "       gg.id \"wzgg.id\",\n" +
                "       gg.name \"wzgg.name\",\n" +
                "       gg.code \"wzgg.code\",\n" +
                "       gg.cz \"wzgg.cz\",\n" +
                "       gg.ggxh \"wzgg.ggxh\",\n" +
                "       gg.dw \"wzgg.dw\",\n" +
                "       jzfs.id \"jzfs.id\",\n" +
                "       jzfs.z \"jzfs.z\",\n" +
                "       nvl(kh.wsdj, 0) \"xscb\",\n" +
                "       kh.js \"js\",\n" +
                "       kh.kkdsl \"sbsl\",\n" +
                "       kh.carNo \"carNo\",\n" +
                "       kh.bindNo \"bindNo\",\n" +
                "       kh.xsxt_xsdh \"xszxh\",\n" +
                "       kh.kkdsl \"kkdsl\",\n" +
                "       nvl(kh.wsdj, 0)/(1 + nvl(cght.sl, 17) * 0.01) \"bhsj\",\n" +
              //  "       nvl(kh.wsdj, 0) \"bhsj\",\n" +
                "       nvl(kh.wsdj, 0) \"hsj\",\n" +
               // "       nvl(kh.wsdj, 0) * (1 + nvl(cght.sl, 17) * 0.01) \"hsj\",\n" +
                "       (nvl(kh.wsdj, 0)/(1 + nvl(cght.sl, 17) * 0.01))*nvl(kh.kkdsl, 0) \"bhse\",\n" +
                "       nvl(kh.wsdj, 0) * nvl(kh.kkdsl, 0) \"hse\"\n" +

               //  "       nvl(kh.wsdj, 0) * nvl(kh.kkdsl, 0) \"bhse\",\n" +
               // "       (nvl(kh.wsdj, 0) * (1 + nvl(cght.sl, 17) * 0.01)) * nvl(kh.kkdsl, 0) \"hse\"\n" +
                "  from t_biz_ht    xsht,\n" +
                "       t_biz_ht    cght,\n" +
                "       t_biz_cgd   cgd,\n" +
                "       t_biz_cgdmx cgdmx,\n" +
                "       t_sys_sjzdz jzfs,\n" +
                "       t_biz_kh    kh,\n" +
              //  "       t_biz_htmx  htmx,\n" +
                "       t_sys_gg    gg\n" +
                " where xsht.zddhcght_id = cght.id\n" +
                "   and cght.id = cgd.cght_id\n" +
                "   and cgd.id = cgdmx.cgd_id\n" +
               // "   and htmx.ht_id=xsht.id\n" +
                "   and cgdmx.szjdz_jzfs_id = jzfs.id\n" +
                "   and cgdmx.id = kh.cgdmx_id\n" +
                "   and kh.jtwz_id = gg.id and  kh.kkdsl <> 0 and kh.ybc=0";


        String whereSql = "";
        if (!condition.isEmpty())
            whereSql += " and " + StringUtils.join(condition, " and ");
        String countSql = sqlCountBase + " ( " + sqlBase + whereSql + " ) ";
        String resultSql = sqlBase + whereSql;
        BigDecimal count = (BigDecimal) createSQLQuery(countSql).setProperties(param).uniqueResult();
        page.setTotalCount(count.intValue());
        if (count.intValue() != 0) {
            List<Map> list = createSQLQuery(resultSql)
                    .setProperties(param)
                    .setFirstResult((pageNo - 1) * pageSize)
                    .setMaxResults(pageSize)
                    .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
                    .list();
            page.setResult(list);
        } else {
            page.setResult(new ArrayList<Map>());
        }
        return page;
    }

    public Page<Map> findAllXsHtTj(String htbm, String xhtbm, Long khId, Date fhrqStart, Date fhrqEnd, String zt, int pageNo, int pageSize) {
        Page<Map> page = new Page<Map>();
        Map<String, Object> param = new HashMap<String, Object>();
        List<String> condition = new ArrayList<String>();
        String sqlBase = "select a.xid as \"xid\",a.cid as \"cid\", a.htbm as \"htbm\",a.xhtbm as \"xhtbm\",a.kh as \"kh\",a.chtbm as \"chtbm\",a.zt as \"zt\" ,a.qdrq as \"qdrq\",a.fksj as \"fksj\",  " +
                " a.sbsl as \"sbsl\",a.cgje as \"cgje\",a.rksl as \"rksl\",a.cksl as \"cksl\",a.ghje as \"ghje\", " +
                " a.shje as \"shje\" , (a.rksl-a.cksl) as \"sysl\" from (" +
                " select xsht.id, xsht.id as xid, cght.id as cid,xsht.htbm,xsht.xsxt_htbm as xhtbm,cght.htbm as chtbm,cght.zt as zt,to_char(cght.fksj,'yyyy-MM-dd') as fksj," +
                " (select max(gs.qc) from t_sys_gsjg gs where gs.id = xsht.hzf_id) as kh, to_char(cght.qdrq,'yyyy-MM-dd') as qdrq," +
                " (select max(gs.id) from t_sys_gsjg gs where gs.id = xsht.hzf_id) as khId, cght.qdrq as rq," +
                " (select sum(mx.sbsl) from t_biz_htmx mx where mx.ht_id = cght.id) as sbsl,to_char(cght.fkrq,'yyyy-MM-dd') as fkrq," +
                " (select round(sum(mx.sbsl*mx.hsj),2) from t_biz_htmx mx where mx.ht_id = cght.id) as cgje," +
                " (select round(nvl(sum(kh.rksl), 0), 6)   from t_biz_kh kh  where kh.cght_id = cght.id) as rksl, " +
                " (select round(nvl(sum(jf.zl),0),6) from t_biz_jfmx jf where jf.xsht_id = xsht.id ) as cksl," +
                " (select round(nvl(sum(jf.zjfpje),0),2) from t_biz_jfmx jf where jf.xsht_id = xsht.id) as ghje, " +
                " (select round(nvl(sum(nvl(jf.ghje,0) + nvl(jf.dlfje,0) + nvl(jf.txje,0) + nvl(jf.zjzyf,0)),0),2) from t_biz_jfmx jf where jf.xsht_id = xsht.id) as shje " +
                " from t_biz_ht xsht,t_biz_ht cght where xsht.type = 'xsht' and xsht.zddhcght_id = cght.id and xsht.zt !='ybc' order by xsht.date_created desc) a" +
                "  where 1=1 and  a.id in (select distinct xsht_id from t_biz_xsd xsd where";

        String dataRegion = RegionForBgDao.createSQLDataRegion(null, "xsd", false);
        if (StringUtils.isNotEmpty(dataRegion))
            condition.add(dataRegion + ")");

        if (StringUtils.isNotEmpty(htbm)) {
            condition.add(" a.htbm like :htbm ");
            param.put("htbm", "%" + htbm + "%");
        }

        if (StringUtils.isNotEmpty(xhtbm)) {
            condition.add(" a.xhtbm like :xhtbm ");
            param.put("xhtbm", "%" + xhtbm + "%");
        }

        if (null != khId) {
            condition.add(" a.khId=:khId ");
            param.put("khId", khId);
        }
        if (fhrqStart != null) {
            condition.add(" a.rq>= :fhrqStart ");
            param.put("fhrqStart", sDaysAfterNowDate(fhrqStart));
        }

        if (fhrqEnd != null) {
            condition.add(" a.rq<= :fhrqEnd ");
            param.put("fhrqEnd", jDaysAfterNowDate(fhrqEnd));
        }

        if (StringUtils.isNotEmpty(zt)) {
            condition.add(" a.zt=:zt ");
            param.put("zt", zt);
        }


        String whereSql = "";
        if (!condition.isEmpty())
            whereSql = StringUtils.join(condition, " and ");

        String countSql = "select count(1) from (" + sqlBase + whereSql + ") ";
        String resultSql = sqlBase + whereSql;
        BigDecimal count = (BigDecimal) createSQLQuery(countSql).setProperties(param).uniqueResult();
        page.setTotalCount(count.intValue());
        if (count.intValue() != 0) {
            List<Map> list = createSQLQuery(resultSql)
                    .setProperties(param)
                    .setFirstResult((pageNo - 1) * pageSize)
                    .setMaxResults(pageSize)
                    .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
                    .list();
            page.setResult(list);
        } else {
            page.setResult(new ArrayList<Map>());
        }
        return page;
    }
}
